package dao;
import model.Rating;
import model.User;
import model.Goods;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import utils.DataSourceUtils;

import java.sql.SQLException;
import java.util.List;

/**
 * 商品评分数据访问类
 */
public class RatingDao {
    
    /**
     * 添加评分
     * @param rating 评分对象
     * @throws SQLException SQL异常
     */
    public void addRating(Rating rating) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "insert into rating(user_id, goods_id, score, comment, create_time) values(?, ?, ?, ?, now())";
        r.update(sql, rating.getUser_id(), rating.getGoods_id(), rating.getScore(), rating.getComment());
    }
    
    /**
     * 更新评分
     * @param rating 评分对象
     * @throws SQLException SQL异常
     */
    public void updateRating(Rating rating) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "update rating set score=?, comment=?, create_time=now() where user_id=? and goods_id=?";
        r.update(sql, rating.getScore(), rating.getComment(), rating.getUser_id(), rating.getGoods_id());
    }
    
    /**
     * 删除评分
     * @param user_id 用户ID
     * @param goods_id 商品ID
     * @throws SQLException SQL异常
     */
    public void deleteRating(int user_id, int goods_id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "delete from rating where user_id=? and goods_id=?";
        r.update(sql, user_id, goods_id);
    }
    
    /**
     * 检查用户是否已对商品评分
     * @param user_id 用户ID
     * @param goods_id 商品ID
     * @return 是否已评分
     * @throws SQLException SQL异常
     */
    public boolean isRated(int user_id, int goods_id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select count(*) from rating where user_id=? and goods_id=?";
        Number count = (Number)r.query(sql, new ScalarHandler(), user_id, goods_id);
        return count.intValue()>0;
    }
    
    /**
     * 根据用户ID和商品ID获取评分
     * @param user_id 用户ID
     * @param goods_id 商品ID
     * @return 评分对象
     * @throws SQLException SQL异常
     */
    public Rating getRatingByUserIdAndGoodsId(int user_id, int goods_id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select * from rating where user_id=? and goods_id=?";
        return r.query(sql, new BeanHandler<Rating>(Rating.class), user_id, goods_id);
    }
    
    /**
     * 根据商品ID获取评分列表
     * @param goods_id 商品ID
     * @return 评分列表
     * @throws SQLException SQL异常
     */
    public List<Rating> getRatingsByGoodsId(int goods_id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select r.*, u.username, u.name from rating r, user u where r.user_id=u.id and r.goods_id=? order by r.create_time desc";
        List<Rating> ratingList = r.query(sql, new BeanListHandler<Rating>(Rating.class), goods_id);
        for(Rating rating : ratingList) {
            // 设置用户信息
            User user = new User();
            user.setId(rating.getUser_id());
            rating.setUser(user);
        }
        return ratingList;
    }
    
    /**
     * 根据商品ID获取平均评分
     * @param goods_id 商品ID
     * @return 平均评分
     * @throws SQLException SQL异常
     */
    public double getAverageScoreByGoodsId(int goods_id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select avg(score) from rating where goods_id=?";
        Object result = r.query(sql, new ScalarHandler(), goods_id);
        if(result == null) {
            return 0;
        }
        return ((Number)result).doubleValue();
    }
    
    /**
     * 根据商品ID获取评分数量
     * @param goods_id 商品ID
     * @return 评分数量
     * @throws SQLException SQL异常
     */
    public int getRatingCountByGoodsId(int goods_id) throws SQLException {
        QueryRunner r = new QueryRunner(DataSourceUtils.getDataSource());
        String sql = "select count(*) from rating where goods_id=?";
        Number count = (Number)r.query(sql, new ScalarHandler(), goods_id);
        return count.intValue();
    }
} 